﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SQLite;

namespace WCFService
{
    public class OrdersDA
    {
        SQLiteDA sqliteDA = new SQLiteDA();

        public List<OrderInfo> GetAllOrdersInfo()
        {
            List<OrderInfo> list = new List<OrderInfo>();
            OrderInfo orderInfo = null;
            var sql = "select * from orders";
            DataTable dt = sqliteDA.GetDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                orderInfo = new OrderInfo();
                orderInfo.RowID = Int32.Parse(dt.Rows[0]["RowID"].ToString());
                orderInfo.OrderID = dt.Rows[0]["OrderID"].ToString();
                orderInfo.ItemID = dt.Rows[0]["ItemID"].ToString();
                orderInfo.Qty = Int32.Parse(dt.Rows[0]["Qty"].ToString());
                orderInfo.Price = decimal.Parse(dt.Rows[0]["Price"].ToString());
                orderInfo.OrderDate = DateTime.Parse(dt.Rows[0]["OrderDate"].ToString());
                orderInfo.Status = dt.Rows[0]["Status"].ToString();
                orderInfo.CreatedBy = dt.Rows[0]["CreatedBy"].ToString();
                orderInfo.CreatedDate = DateTime.Parse(dt.Rows[0]["CreatedDate"].ToString());
                list.Add(orderInfo);
            }
            return list;
        }

        public OrderInfo GetOrdersInfoByID(string orderID)
        {
            OrderInfo orderInfo = null;
            var sql = string.Format("select * from orders where order_id='{0}'", orderID);
            DataTable dt = sqliteDA.GetDataTable(sql);
            if (dt != null && dt.Rows.Count > 0)
            {
                orderInfo = new OrderInfo();
                orderInfo.RowID = Int32.Parse(dt.Rows[0]["RowID"].ToString());
                orderInfo.OrderID = dt.Rows[0]["OrderID"].ToString();
                orderInfo.ItemID = dt.Rows[0]["ItemID"].ToString();
                orderInfo.Qty = Int32.Parse(dt.Rows[0]["Qty"].ToString());
                orderInfo.Price = decimal.Parse(dt.Rows[0]["Price"].ToString());
                orderInfo.OrderDate = DateTime.Parse(dt.Rows[0]["OrderDate"].ToString());
                orderInfo.Status = dt.Rows[0]["Status"].ToString();
                orderInfo.CreatedBy = dt.Rows[0]["CreatedBy"].ToString();
                orderInfo.CreatedDate = DateTime.Parse(dt.Rows[0]["CreatedDate"].ToString());
            }
            return orderInfo;
        }

        public int InsertOrderInfo(OrderInfo orderInfo)
        {
            var getOrderID = "select max(OrderID) from Orders";
            string result = sqliteDA.ExecuteScalar(getOrderID);
            if (!string.IsNullOrEmpty(result))
            {
                orderInfo.OrderID = (Int32.Parse(result) + 1).ToString();
            }
            else
            {
                orderInfo.OrderID = "10000000";
            }

            var sql = "insert into Orders values(@RowID,@OrderID,@ItemID,@Qty,@Price,@OrderDate,@Status,@CreatedBy,@CreatedDate)";
            var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("RowID", null),
                    new SQLiteParameter("OrderID", orderInfo.OrderID),
                    new SQLiteParameter("ItemID", orderInfo.ItemID),
                    new SQLiteParameter("Qty", orderInfo.Qty),
                    new SQLiteParameter("Price", orderInfo.Price),
                    new SQLiteParameter("OrderDate", orderInfo.OrderDate),
                    new SQLiteParameter("Status", orderInfo.Status),
                    new SQLiteParameter("CreatedBy", orderInfo.CreatedBy),
                    new SQLiteParameter("CreatedDate", orderInfo.CreatedDate)
                };
            return sqliteDA.ExecuteNonQuery(sql, cmdparams);
        }

        public int UpdateOrderInfo(OrderInfo orderInfo)
        {
            var sql = "Update Orders Set Qty=@Qty Where OrderID=@OrderID";
            var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("OrderID", orderInfo.OrderID),
                    new SQLiteParameter("Qty", orderInfo.Qty)
                };
            return sqliteDA.ExecuteNonQuery(sql, cmdparams);
        }

        public int DeleteOrderInfo(string orderID)
        {
            var sql = "delete Orders Where OrderID=@OrderID";
            var cmdparams = new List<SQLiteParameter>()
                {
                    new SQLiteParameter("OrderID", orderID)
                };
            return sqliteDA.ExecuteNonQuery(sql, cmdparams);
        }
    }
}
